package digitaldreamers.libraries.games.servers.databasemanagement.concrete.mysql;

import digitaldreamers.libraries.games.servers.databasemanagement.abstracts.IDatabaseUtility;
import digitaldreamers.libraries.games.servers.databasemanagement.concrete.DatabaseManagement.IsolationLevel;
import digitaldreamers.libraries.games.servers.databasemanagement.concrete.SqlParameter;
import digitaldreamers.libraries.games.servers.databasemanagement.concrete.SqlParameter.ParameterDirection;
import digitaldreamers.libraries.games.servers.databasemanagement.concrete.SqlParameter.SqlDataType;
import java.math.BigDecimal;
import java.sql.*;
/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */

/**
 *
 * @author DaoLe
 */
public class MySqlDatabaseUtility implements IDatabaseUtility {
    // <editor-fold defaultstate="collapsed" desc="Command type enum">
    private static enum CommandType {
        Text,
        StoredProcedure
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="Constants">
    private static final String DEFAULT_SERVER_NAME = "localhost";
    private static final int DEFAULT_PORT = 3306;
    private static final String DEFAULT_USERNAME = "root";
    private static final int DEFAULT_TIMEOUT = 30;
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="Member variables">
    private Connection mConnection;
    private boolean mIsTransactionStarted;
    private boolean mIsCloseOnCompletion;
    //private String mConnectionString = "";
    
    private String mServerName;
    private int mPort;
    private String mDatabaseName;
    private String mUsername;
    private String mPassword;
    private int mCommandTimeout;
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Getters/Setters">
    @Override
    public String getServerName() {
        return mServerName;
    }
    

    @Override
    public void setServerName(String pServerName) {
        if (pServerName == null || pServerName.equals("")) {
            mServerName = DEFAULT_SERVER_NAME;
        } else {
            mServerName = pServerName;
        }
        //m_strConnectionString = makeConnectionString(mServerName, mPort, mDatabaseName);
    }
    
    @Override
    public int getPort() {
        return mPort;
    }

    @Override
    public void setPort(int pPort) {
        if (pPort < 0) {
            mPort = DEFAULT_PORT;
        } else {
            mPort = pPort;
        }
        //m_strConnectionString = makeConnectionString(mServerName, mPort, mDatabaseName);
    }

    // <editor-fold defaultstate="collapsed" desc="Database name">
    @Override
    public String getDatabaseName() {
        return mDatabaseName;
    }

    @Override
    public void setDatabaseName(String pDatabaseName) {
        if (pDatabaseName == null) {
            mDatabaseName = "";
        } else {
            mDatabaseName = pDatabaseName;
        }
        //m_strConnectionString = makeConnectionString(mServerName, mPort, mDatabaseName);
    }
    
    @Override
    public String getUsername() {
        return mUsername;
    }

    @Override
    public void setUsername(String pUsername) {
        if (pUsername == null || pUsername.equals("")) {
            mUsername = DEFAULT_USERNAME;
        } else {
            mUsername = pUsername;
        }
    }

    @Override
    public String getPassword() {
        return mPassword;
    }

    @Override
    public void setPassword(String pPassword) {
        if (pPassword == null) {
            mPassword = "";
        } else {
            mPassword = pPassword;
        }
    }
    
    @Override
    public int getCommandTimeout() {
        return mCommandTimeout;
    }

    @Override
    public void setCommandTimeout(int pCommandTimeout) {
        if (pCommandTimeout < 0) {
            mCommandTimeout = DEFAULT_TIMEOUT;
        } else {
            mCommandTimeout = pCommandTimeout;
        }
    }
    
    // <editor-fold defaultstate="collapsed" desc="Constructors">
    
    // <editor-fold defaultstate="collapsed" desc="MySqlDatabaseUtility()">
    public MySqlDatabaseUtility() {
        this(DEFAULT_SERVER_NAME, DEFAULT_PORT, "", DEFAULT_USERNAME, "", DEFAULT_TIMEOUT);
    }
    // </editor-fold>
    public MySqlDatabaseUtility(String pServerName, int pPort, String pDatabaseName, String pUsername, String pPassword, int pCommandTimeout) {
        mServerName = pServerName;
        mPort = pPort;
        mDatabaseName = pDatabaseName;
        mUsername = pUsername;
        mPassword = pPassword;
        mCommandTimeout = pCommandTimeout;
        //m_strConnectionString = makeConnectionString(mServerName, mPort, mDatabaseName);
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="Methods">
    
    // <editor-fold defaultstate="collapsed" desc="Static Methods">
    private static String makeConnectionString(String pServerName, int pPort, String pDatabaseName) {
        StringBuilder connectionString = new StringBuilder();
        connectionString.append("jdbc:mysql://");
        connectionString.append(pServerName);
        connectionString.append(":");
        connectionString.append(pPort);
        connectionString.append("/");
        connectionString.append(pDatabaseName);
        return connectionString.toString();
    }
    
    private static String makeStoredProcedureCallingStatement(String pStoredProcedureName, int pParameterNumber) {
        StringBuilder statement = new StringBuilder();
        statement.append("{call ");
        statement.append(pStoredProcedureName);
        statement.append("(");
        for (int i = 0; i < pParameterNumber; i++) {
            if (i < pParameterNumber - 1) {
                statement.append("?, ");
            } else {
                statement.append("?");
            }
        }
        statement.append(")");
        statement.append("}");
        return statement.toString();
    }
    
    private static void setParameter(CallableStatement pStatement, String pParameterName, SqlDataType pSqlDataType, Object pValue) throws SQLException {
        switch (pSqlDataType) {
            
            // Integer
            case TinyInt:
                if (pValue != null) {
                    pStatement.setByte(pParameterName, (Byte) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.TINYINT);
                }
                break;
            case SmallInt:
                if (pValue != null) {
                    pStatement.setShort(pParameterName, (Short) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.SMALLINT);
                }
                break;
            case MediumInt:
            case Int:
                if (pValue != null) {
                    pStatement.setInt(pParameterName, (Integer) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.INTEGER);
                }
                break;
            case BigInt:
                if (pValue != null) {
                    pStatement.setLong(pParameterName, (Long) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.BIGINT);
                }
                break;

            // Floating point
            case Float:
                if (pValue != null) {
                    pStatement.setFloat(pParameterName, (Float) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.FLOAT);
                }
                break;
            case Double:
                if (pValue != null) {
                    pStatement.setDouble(pParameterName, (Double) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.DOUBLE);
                }
                break;

            // Decimal
            case Decimal:
                if (pValue != null) {
                    pStatement.setBigDecimal(pParameterName, (BigDecimal) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.DECIMAL);
                }
                break;

            // Date and time
            case Date:
                if (pValue != null) {
                    pStatement.setDate(pParameterName, (Date) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.DATE);
                }
                break;
            case Time:
                if (pValue != null) {
                    pStatement.setTime(pParameterName, (Time) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.TIME);
                }
                break;
            case DateTime:
            case Timestamp:
                if (pValue != null) {
                    pStatement.setTimestamp(pParameterName, (Timestamp) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.TIMESTAMP);
                }
                break;

            // String
            case Char:
                if (pValue != null) {
                    pStatement.setString(pParameterName, (String) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.CHAR);
                }
                break;
            case NChar:
                if (pValue != null) {
                    pStatement.setString(pParameterName, (String) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.NCHAR);
                }
                break;
            case VarChar:
            case Text:
                if (pValue != null) {
                    pStatement.setString(pParameterName, (String) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.VARCHAR);
                }
                break;
            case NVarChar:
            case NText:
                if (pValue != null) {
                    pStatement.setString(pParameterName, (String) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.NVARCHAR);
                }
                break;

            // Binary
            case Bit:
                if (pValue != null) {
                    pStatement.setBoolean(pParameterName, (Boolean) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.BIT);
                }
                break;
            case Binary:
                if (pValue != null) {
                    pStatement.setBytes(pParameterName, (byte[]) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.BINARY);
                }
                break;
            case VarBinary:
                if (pValue != null) {
                    pStatement.setBytes(pParameterName, (byte[]) pValue);
                } else {
                    pStatement.setNull(pParameterName, java.sql.Types.VARBINARY);
                }
                break;
            default:
                break;
        }
    }
    
    private static void registerOutParameter(CallableStatement pStatement, String pParameterName, CommandType pCommandType, ParameterDirection pDirection, SqlDataType pSqlDataType) throws SQLException {
        if (pCommandType == CommandType.StoredProcedure) {
            if (pDirection == ParameterDirection.OutPut) {
                switch (pSqlDataType) {
                    
                    // Integer
                    case TinyInt:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.TINYINT);
                        break;
                    case SmallInt:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.SMALLINT);
                        break;
                    case MediumInt:
                    case Int:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.INTEGER);
                        break;
                    case BigInt:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.BIGINT);
                        break;

                    // Floating point
                    case Float:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.FLOAT);
                        break;
                    case Double:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.DOUBLE);
                        break;

                    // Decimal
                    case Decimal:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.DECIMAL, 15);
                        break;

                    // Date and time
                    case Date:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.DATE);
                        break;
                    case Time:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.TIME);
                        break;
                    case DateTime:
                    case Timestamp:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.TIMESTAMP);
                        break;

                    // String
                    case Char:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.CHAR);
                        break;
                    case NChar:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.NCHAR);
                        break;
                    case VarChar:
                    case Text:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.VARCHAR);
                        break;
                    case NVarChar:
                    case NText:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.NVARCHAR);
                        break;

                    // Binary
                    case Bit:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.BIT);
                        break;
                    case Binary:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.BINARY);
                        break;
                    case VarBinary:
                        pStatement.registerOutParameter(pParameterName, java.sql.Types.VARBINARY);
                        break;
                    default:
                        break;
                }
            }
        }
    }
    
    private static Connection connect(String pServerName, int pPort, String pDatabaseName, String pUsername, String pPassword) throws SQLException {
        String connectionString = makeConnectionString(pServerName, pPort, pDatabaseName);
        Connection connection = DriverManager.getConnection(connectionString, pUsername, pPassword);
        return connection;
    }
    
    private static CallableStatement createStatement(Connection pConnection, String pSqlStatement, CommandType pCommandType, SqlParameter[] pParameters, int pCommandTimeout) throws SQLException {
        CallableStatement callableStatement = null;
        switch (pCommandType) {
            case Text:
                callableStatement = new NamedParameterStatement(pConnection, pSqlStatement);
                break;
            case StoredProcedure:
                int length = 0;
                if (pParameters != null) {
                    length = pParameters.length;
                }
                String statement = makeStoredProcedureCallingStatement(pSqlStatement, length);
                callableStatement = pConnection.prepareCall(statement);
                break;
            default:
                break;
        }
        if (pParameters != null) {
            for (int i = 0; i < pParameters.length; i++) {

                // Get parameter info.
                String parameterName = pParameters[i].getParameterName();
                ParameterDirection direction = pParameters[i].getParameterDirection();
                SqlDataType sqlDataType = pParameters[i].getSqlDataType();
                Object value = pParameters[i].getValue();

                // Set parameter.
                setParameter(callableStatement, parameterName, sqlDataType, value);

                // Register out parameter.
                registerOutParameter(callableStatement, parameterName, pCommandType, direction, sqlDataType);
            }
        }
        if(callableStatement != null) {
            callableStatement.setQueryTimeout(pCommandTimeout);
        }
        return callableStatement;
    }
    
    private static void getOutParameterValues(CallableStatement pStatement, SqlParameter[] pParameters) throws SQLException {
        if (pParameters != null) {
            for (int i = 0; i < pParameters.length; i++) {
                ParameterDirection direction = pParameters[i].getParameterDirection();
                if (direction == ParameterDirection.OutPut) {
                    String parameterName = pParameters[i].getParameterName();
                    SqlDataType sqlDataType = pParameters[i].getSqlDataType();
                    Object value = null;
                    switch (sqlDataType) {

                        // Integer
                        case TinyInt:
                            value = pStatement.getByte(parameterName);
                            break;
                        case SmallInt:
                            value = pStatement.getShort(parameterName);
                            break;
                        case MediumInt:
                        case Int:
                            value = pStatement.getInt(parameterName);
                            break;
                        case BigInt:
                            value = pStatement.getLong(parameterName);
                            break;

                        // Floating point
                        case Float:
                            value = pStatement.getFloat(parameterName);
                            break;
                        case Double:
                            value = pStatement.getDouble(parameterName);
                            break;

                        // Decimal
                        case Decimal:
                            value = pStatement.getBigDecimal(parameterName);
                            break;

                        // Date and time
                        case Date:
                            value = pStatement.getDate(parameterName);
                            break;
                        case Time:
                            value = pStatement.getTime(parameterName);
                            break;
                        case DateTime:
                        case Timestamp:
                            value = pStatement.getTimestamp(parameterName);
                            break;

                        // String
                        case Char:
                        case NChar:
                        case VarChar:
                        case Text:
                        case NVarChar:
                        case NText:
                            value = pStatement.getString(parameterName);
                            break;

                        // Binary
                        case Bit:
                            value = pStatement.getBoolean(parameterName);
                            break;
                        case Binary:
                        case VarBinary:
                            value = pStatement.getBytes(parameterName);
                            break;
                        default:
                            break;
                    }
                    pParameters[i].setValue(value);
                }
            }
        }
    }
    
    @Override
    public void closeOnCompletion(boolean pIsCloseOnCompletion) {
        mIsCloseOnCompletion = pIsCloseOnCompletion;
    }
    
    @Override
    public boolean isCloseOnCompletion() {
        return mIsCloseOnCompletion;
    }
    
    @Override
    public boolean isTransactionStarted() {
        return mIsTransactionStarted;
    }
    
    @Override
    public ResultSet executeQuery(String pSqlStatement) throws SQLException {
        return executeQuery(pSqlStatement, null);
    }
    
    @Override
    public ResultSet executeQuery(String pSqlStatement, SqlParameter[] pParameters) throws SQLException {
        if (!mIsTransactionStarted && mConnection == null) {
            mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
        }
        CallableStatement statement = createStatement(mConnection, pSqlStatement, CommandType.Text, pParameters, mCommandTimeout);
        ResultSet resultSet = statement.executeQuery();
        if (!mIsTransactionStarted && mIsCloseOnCompletion) {
            mConnection.close();
            mConnection = null;
        }
        return resultSet;
    }
    
    @Override
    public int executeUpdate(String pSqlStatement) throws SQLException {
        return executeUpdate(pSqlStatement, null);
    }
    
    @Override
    public int executeUpdate(String pSqlStatement, SqlParameter[] pParameters) throws SQLException {
        if (!mIsTransactionStarted && mConnection == null) {
            mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
        }
        CallableStatement statement = createStatement(mConnection, pSqlStatement, CommandType.Text, pParameters, mCommandTimeout);
        int rowCount = statement.executeUpdate();
        if (!mIsTransactionStarted && mIsCloseOnCompletion) {
            mConnection.close();
            mConnection = null;
        }
        return rowCount;
    }
    
    @Override
    public ResultSet executeQuerySP(String pStoredProcedureName) throws SQLException {
        return executeQuerySP(pStoredProcedureName, null);
    }
    
    @Override
    public ResultSet executeQuerySP(String pStoredProcedureName, SqlParameter[] pParameters) throws SQLException {
        if (!mIsTransactionStarted && mConnection == null) {
            mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
        }
        CallableStatement statement = createStatement(mConnection, pStoredProcedureName, CommandType.StoredProcedure, pParameters, mCommandTimeout);
        ResultSet resultSet = statement.executeQuery();
        getOutParameterValues(statement, pParameters);
        if (!mIsTransactionStarted && mIsCloseOnCompletion) {
            mConnection.close();
            mConnection = null;
        }
        return resultSet;
    }
    
    @Override
    public int executeUpdateSP(String pStoredProcedureName) throws SQLException {
        return executeUpdateSP(pStoredProcedureName, null);
    }
    
    @Override
    public int executeUpdateSP(String pStoredProcedureName, SqlParameter[] pParameters) throws SQLException {
        if (!mIsTransactionStarted && mConnection == null) {
            mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
        }
        CallableStatement statement = createStatement(mConnection, pStoredProcedureName, CommandType.StoredProcedure, pParameters, mCommandTimeout);
        int rowCount = statement.executeUpdate();
        getOutParameterValues(statement, pParameters);
        if (!mIsTransactionStarted && mIsCloseOnCompletion) {
            mConnection.close();
            mConnection = null;
        }
        return rowCount;
    }
    
    @Override
    public Statement executeSP(String pStoredProcedureName) throws SQLException {
        return executeSP(pStoredProcedureName, null);
    }
    
    @Override
    public Statement executeSP(String pStoredProcedureName, SqlParameter[] pParameters) throws SQLException {
        if (!mIsTransactionStarted && mConnection == null) {
            mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
        }
        CallableStatement statement = createStatement(mConnection, pStoredProcedureName, CommandType.StoredProcedure, pParameters, mCommandTimeout);
        statement.execute();
        getOutParameterValues(statement, pParameters);
        if (!mIsTransactionStarted && mIsCloseOnCompletion) {
            mConnection.close();
            mConnection = null;
        }
        return statement;
    }
    
    @Override
    public void startTransaction(IsolationLevel pIsolationLevel) throws SQLException {
        if (!mIsTransactionStarted) {
            this.mIsTransactionStarted = true;
            if(mConnection == null) {
                mConnection = connect(mServerName, mPort, mDatabaseName, mUsername, mPassword);
            }
            mConnection.setAutoCommit(false);
            switch(pIsolationLevel) {
                case TRANSACTION_READ_COMMITTED:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_READ_COMMITTED);
                    break;
                case TRANSACTION_READ_UNCOMMITTED:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_READ_UNCOMMITTED);
                    break;
                case TRANSACTION_NONE:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_NONE);
                    break;
                case TRANSACTION_REPEATABLE_READ:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_REPEATABLE_READ);
                    break;
                case TRANSACTION_SERIALIZABLE:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_SERIALIZABLE);
                    break;
                default:
                    mConnection.setTransactionIsolation (Connection.TRANSACTION_READ_COMMITTED);
                    break;
            }
        }
    }
    
    @Override
    public void commitTransaction() throws SQLException {
        if (mIsTransactionStarted) {
            if (mConnection != null) {
                mConnection.commit();
                mIsTransactionStarted = false;
                if(mIsCloseOnCompletion) {
                    mConnection.close();
                    mConnection = null;
                }
            }
        }
    }
    
    @Override
    public void rollbackTransaction() throws SQLException {
        if (mIsTransactionStarted) {
            if (mConnection != null) {
                mConnection.rollback();
                mIsTransactionStarted = false;
                if(mIsCloseOnCompletion) {
                    mConnection.close();
                    mConnection = null;
                }
            }
        }
    }
    // </editor-fold>
}